package com.daimler.itffo.tables;

import java.util.InputMismatchException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.daimler.itffo.uvs.kern.IGenerator;
import com.daimler.itffo.uvs.kern.SQLStatment;
import com.daimler.itffo.uvs.kern.SQLWriter;

public class V0BRB035 implements IGenerator {
	public static int COL_B035_FZ_WHC = 1;
	public static int COL_B035_FZ_ID_NR = 2;
	public static int COL_B035_VDAT = 3;
	public static int COL_B035_EZ = 37;
	public static int COL_B035_SO_LAND = 6;
	public static int COL_B035_HERST_SL = 92;
	public static int COL_B035_KM_STAND = 15;
	public static int COL_B035_SP_CODE = 9;
	public static int COL_B035_NP_NPREISD = 50;
	public static int COL_B035_VK_NPREISD = 48;
	private String messages;
	private String fz_whc;
	private String fzIdNr;
	private String vDat;
	private String eZ;
	private String soLand;
	private String herstelS;
	private int kmStand;
	private String spCode;
	private int nPreisd;
	private int nVPreisd;

	public V0BRB035(String whc, String fzId, String vDat, String land, int km,
			String spCode, String eZ, int vKpreisd, int nPreisd, String herstel) {
		super();
		this.fz_whc = whc;
		this.fzIdNr = fzId;
		this.vDat = vDat;
		this.soLand = land;
		this.kmStand = km;
		this.spCode = spCode;
		this.eZ = eZ;
		this.nVPreisd = vKpreisd;
		this.nPreisd = nPreisd;
		this.herstelS = herstel;

	}

	public V0BRB035() {

	}

	@Override
	public String generate(Sheet sheet, String folder, String str, String uStr)
			throws Exception {
		SQLWriter fileWriterV0BRB035EU;
		fileWriterV0BRB035EU = new SQLWriter();
		fileWriterV0BRB035EU.openFile(folder + '\\'
				+ SQLWriter.FILENAME_B035_EU);
		String colChanged;
		String colFz_whc;
		String colFzIdNr;
		String colVDat;
		String colEz;
		String colSoLand;
		String colHerstelS;
		int colKmStand;
		String colSpCode;
		int colNPreiD;
		int colNvPreisD;
		V0BRB035 b035;
		Row row;

		SQLStatment sqlid;
		sqlid = new SQLStatment();
		// sqlid.addFragment("SET CURRENT SQLID='" + str + "';");
		sqlid.addFragment(SQLWriter.NEWLINE);
		fileWriterV0BRB035EU.write(sqlid);
		int active_row = 1;
		int action;
		int rows_ignored = 0;
		int row_inserted = 0;
		int row_deleted = 0;
		int row_count = 0;
		while (true) {
			row = sheet.getRow(active_row);
			if (isRowTheLastRow(row)) {
				fileWriterV0BRB035EU.write(commitStatment());

				break;
			}

			colChanged = getValueOfStringCell(row.getCell(COL_TABLE_CHANGED));
			action = checkAction(colChanged);
			if (action == ACTION_ERROR) {
				messages = "Fehler: Die (Steuer-)Zelle "
						+ getStringPos(row.getCell(COL_TABLE_CHANGED))
						+ " der folgende Wert enthalten:\n";
				messages += "Update: 'U'\n";
				messages += "Keine Aktion: ''\n";
				return messages;
			}

			if (action != ACTION_NONE) {
				colFz_whc = getValueOfStringCell(row.getCell(COL_B035_FZ_WHC));
				colFzIdNr = getValueOfStringCell(row.getCell(COL_B035_FZ_ID_NR));
				colVDat = getValueOfStringCell(row.getCell(COL_B035_VDAT));
				colEz = getValueOfStringCell(row.getCell(COL_B035_EZ));
				colSoLand = getValueOfStringCell(row.getCell(COL_B035_SO_LAND));
				colHerstelS = getValueOfStringCell(row
						.getCell(COL_B035_HERST_SL));
				colKmStand = getValueOfIntCell(row.getCell(COL_B035_KM_STAND));
				colSpCode = getValueOfStringCell(row.getCell(COL_B035_SP_CODE));
				colNPreiD = getValueOfIntCell(row.getCell(COL_B035_NP_NPREISD));
				colNvPreisD = getValueOfIntCell(row
						.getCell(COL_B035_VK_NPREISD));

				b035 = new V0BRB035(colFz_whc, colFzIdNr, colVDat, colSoLand,
						colKmStand, colSpCode, colEz, colNPreiD, colNvPreisD,
						colHerstelS);

				//fileWriterV0BRB035EU.write(b035.createSQLDescription());
				//fileWriterV0BRB035EU.write(b035.createSQLDeleteStatment());

				if (action == ACTION_INSERT_AND_DELETE)
					fileWriterV0BRB035EU.write(b035.createSQLStatment(false));

				row_count++;

				if (row_count % 250 == 0) {
					fileWriterV0BRB035EU.write(b035.commitStatment());
				}

			}

			if (action == ACTION_NONE)
				rows_ignored++;

			if (action == ACTION_DELETE)
				row_deleted++;

			if (action == ACTION_INSERT_AND_DELETE)
				row_inserted++;

			active_row++;
		}
		messages = "Zeilen verarbeitet: " + (active_row - 1) + "\n";
		messages += "Zeilen ignoriert: " + rows_ignored + "\n";
		messages += "Zeilen geloescht: " + row_deleted + "\n";
		messages += "Zeilen eingefuegt: " + row_inserted + "\n";

		fileWriterV0BRB035EU.close();

		return messages;

	}

	@Override
	public String getCellStyleStringRep(int type) {
		switch (type) {
		case Cell.CELL_TYPE_STRING:
			return "TEXT";
		case Cell.CELL_TYPE_NUMERIC:
			return "ZAHL";
		case Cell.CELL_TYPE_FORMULA:
			return "FORMEL";
		case Cell.CELL_TYPE_BLANK:
			return "LEER";
		default:
			break;
		}
		return "UNBEKANNT";
	}

	@Override
	public int checkAction(String action) {
		if (action == null)
			return ACTION_ERROR;
		String upperAction = action.toUpperCase();
		if (ACTION_NONE_STRING.equals(upperAction))
			return ACTION_NONE;

		if (ACTION_DELETE_STRING.equals(upperAction))
			return ACTION_DELETE;

		if (ACTION_INSERT_AND_DELETE_STRING.equals(upperAction))
			return ACTION_INSERT_AND_DELETE;

		return ACTION_ERROR;
	}

	@Override
	public SQLStatment createSQLStatment(boolean uniCode) {
		SQLStatment sql = new SQLStatment();
		sql.addFragment("INSERT INTO V0BRB035 ");
		sql.addFragment("(B035_FZ_WHC, ");
		sql.addFragment("B035_FZ_ID_NR, ");
		sql.addFragment("B035_VDAT, ");
		sql.addFragment("B035_EZ, ");
		sql.addFragment("B035_SO_LAND, ");
		sql.addFragment("B035_HERST_SL, ");
		sql.addFragment("B035_KM_STAND, ");
		sql.addFragment("B035_SP_CODE, ");
		sql.addFragment("B035_NP_NPREISD, ");
		sql.addFragment("B035_VK_NPREISD) ");
		sql.addFragment("VALUES (");
		sql.addValue(this.fz_whc, false);
		sql.addValue(this.fzIdNr, false);
		sql.addValue(this.vDat, false);
		sql.addValue(this.eZ, false);
		sql.addValue(this.soLand, false);
		sql.addValue(this.herstelS, false);
		sql.addValue(this.kmStand, false);
		sql.addValue(this.spCode, false);
		sql.addValue(this.nPreisd, false);
		sql.addValue(this.nVPreisd, true);
		sql.addFragment(SQLWriter.NEWLINE);
		return sql;
	}

	@Override
	public String colToString(int col) {
		switch (col) {
		case 1:
			return "A";
		case 2:
			return "B";
		case 3:
			return "C";
		case 4:
			return "D";
		case 5:
			return "E";
		case 6:
			return "F";
		case 7:
			return "G";
		case 8:
			return "H";
		case 9:
			return "I";
		case 10:
			return "J";
		case 11:
			return "K";
		case 12:
			return "L";
		case 13:
			return "M";
		case 14:
			return "N";
		case 15:
			return "O";
		case 16:
			return "P";

		default:
			break;
		}
		return "Ausserhalb des Verabreitungsbereich";
	}

	@Override
	public boolean isRowTheLastRow(Row row) {
		if (row == null)
			return true;

		Cell bmCell = row.getCell(COL_B035_FZ_WHC);

		if (bmCell == null)
			return true;

		if (bmCell.getCellType() == Cell.CELL_TYPE_BLANK)
			return true;

		if (bmCell.getCellType() == Cell.CELL_TYPE_STRING) {
			String s = getValueOfStringCell(row.getCell(1));
			if (s == null)
				return true;
			if (!s.equals(""))
				return false;
		}
		return true;
	}

	@Override
	public String getValueOfStringCell(Cell cell) throws InputMismatchException {
		String value = new String("");

		if (cell == null)
			return value;

		if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
			return new String("");

		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			value = cell.getStringCellValue();

		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			int i = (int) cell.getNumericCellValue();
			value = String.valueOf(i);
		} else {
			throw new InputMismatchException("Zelle " + getStringPos(cell)
					+ " ist als " + getCellStyleStringRep(cell.getCellType())
					+ " definiert. Dies kann nicht verarbeit werden.");
		}

		if (value == null)
			return new String("");

		return value.trim();
	}

	@Override
	public String getStringPos(Cell cell) {
		return colToString(cell.getColumnIndex() + 1) + "-"
				+ (cell.getRowIndex() + 1);
	}

	@Override
	public int getValueOfIntCell(Cell cell) throws InputMismatchException {
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			String value = getValueOfStringCell(cell);
			if (value.equals("")) {
				throw new InputMismatchException("Zelle " + getStringPos(cell)
						+ " muss einen (Numerischen) Wert enthalten");
			}
			return Integer.valueOf(value).intValue();
		}

		checkCellStyle(cell, Cell.CELL_TYPE_NUMERIC);

		Double d = cell.getNumericCellValue();
		return d.intValue();
	}

	@Override
	public void checkCellStyle(Cell cell, int type)
			throws InputMismatchException {
		if (cell.getCellType() == type)
			return;

		throw new InputMismatchException("Zelle " + getStringPos(cell)
				+ " muss als " + getCellStyleStringRep(type)
				+ " definiert sein.\n" + "Zelle " + getStringPos(cell)
				+ " ist als " + getCellStyleStringRep(cell.getCellType()) + "("
				+ cell.getCellType() + ") definiert"

		);

	}

	@Override
	public SQLStatment commitStatment() {
		SQLStatment commit = new SQLStatment();
		commit.addFragment("COMMIT;" + SQLWriter.NEWLINE);
		return commit;
	}

	@Override
	public SQLStatment createSQLDeleteStatment() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public SQLStatment createSQLDescription() {
		// TODO Auto-generated method stub
		return null;
	}

}
